﻿using Dapper;
using DDD.Core.CQRS;
using DDD.Core.Extensions;
using DDD.Core.Result;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;
using DDD.Common.Extensions;

namespace DDD.Application.Queries.UserQuery.ExportUser;

internal class ExportUserQueryHandler : QueryHandler<ExportUserQuery, ApiResult<byte[]>, byte[]>
{
    private readonly IDBConnectionFactory _dBConnectionFactory;

    public ExportUserQueryHandler(IDBConnectionFactory dBConnectionFactory) : base(dBConnectionFactory)
    {
        _dBConnectionFactory = dBConnectionFactory;
    }

    public override async Task<ApiResult<byte[]>> Handle(ExportUserQuery request, CancellationToken cancellationToken)
    {
        using var dbConnetion = _dBConnectionFactory.GetDbConnection();
        string sql = "SELECT * FROM User";

        var data = await dbConnetion.QueryAsync<ExportUserDto>(sql, cancellationToken);

        var bytes = CreateExcel(GetProperties(), data.ToList());

        return ApiResult<byte[]>.Success(bytes);
    }

    private Dictionary<string, string> GetProperties()
    {
        Dictionary<string, string> props = [];
        var properties = typeof(ExportUserDto).GetProperties();

        if (properties != null && properties.Length != 0)
        {
            foreach (var item in properties)
            {
                props.Add(item.Name, item.GetDescription());
            }
        }

        return props;
    }

    private byte[] CreateExcel<T>(Dictionary<string, string> titleDicionary, IReadOnlyList<T> data)
        where T : class
    {
        using IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("main");

        Type entityType = data[0].GetType();
        PropertyInfo[] entityProperties = entityType.GetProperties();

        //create excel table title row  
        var titleRow = sheet.CreateRow(0);
        for (int i = 0; i < titleDicionary.Count; i++)
        {
            if (i == 0)
                titleRow.CreateCell(0).SetCellValue("序号");

            titleRow.CreateCell(i + 1).SetCellValue(titleDicionary.Values.ToArray()[i]);
        }

        //create excel table data row 
        for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
        {
            var dataRow = sheet.CreateRow(rowIndex + 1);
            dataRow.CreateCell(0).SetCellValue(rowIndex + 1);

            //fill table row cell
            for (int cellIndex = 0; cellIndex < titleDicionary.Count; cellIndex++)
            {
                var prop = titleDicionary.Keys.ToArray()[cellIndex];
                var instanceProp = typeof(T).GetProperty(prop);
                if (instanceProp == null)
                    throw new ArgumentException($"类型{typeof(T).GetGenericTypeName()}不存在属性{prop}");

                var cellData = instanceProp.GetValue(data[rowIndex]);

                dataRow.CreateCell(cellIndex + 1, CellType.String).SetCellValue(cellData?.ToString());
            }
        }

        byte[] buffer = new byte[1024 * 5];
        using MemoryStream ms = new();
        workbook.Write(ms);
        buffer = ms.ToArray();
        ms.Close();

        return buffer;
    }
}
